Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# ARGFR

Within an OLAP DML program, the ARGFR function lets you reference the arguments that are passed to a program. The function returns a group of one or more arguments, beginning with the specified argument number, as a single text value. You can use ARGFR only within a program that is invoked as a command, not as a user-defined function or with a CALL statement.

Note: Typically, users use an ARGUMENT statement to define arguments in a program, thereby negating the need for using the ARGFR function to reference arguments passed to the program. For more information on how to use the ARGUMENT to define arguments that are passed to a program, see "Declaring Arguments that Will be Passed Into a Program" .

Return Value

TEXT

Syntax

ARGFR(n)

Arguments

n

  The number by position of the first argument in the group of arguments you want to reference. `ARGFR(1)` returns the first argument and all subsequent arguments, `ARGFR(2)` returns the second argument and all subsequent arguments, and so forth. When there are fewer than n arguments, ARGFR returns a null value. ARGFR also returns a null value when n is `0` (zero) or negative.

Examples

Example 8-5 Passing Arguments Using ARG and ARGFR

Suppose you have a program that produces a sales report. You want to be able to produce this report for any product and any period of months, so you do not want to limit the `product` and `month` dimensions to specific values in the program. Instead, you can use the LIMIT command using ARG for the `product` argument and an ARGFR function for the `month` argument. This way, these items can be specified when the program is run.

When ARGFR is included in the LIMIT command preceded by an ampersand (`&`), Oracle OLAP substitutes the values of `&ARGFR` before the command is executed and, as a result, treats the whole argument as a phrase of the LIMIT command. The `salesrprt` program has a LIMIT command that includes `&ARGFR`.

    DEFINE salesrpt PROGRAM
    PROGRAM
    PUSH product month district
    TRAP ON cleanup
    LIMIT product TO UPCASE(ARG(1))
    LIMIT month TO &ARGFR(2)
    LIMIT district TO ALL
    REPORT grandtotals DOWN district sales
    cleanup:
    POP product month district
    END

The command line for the `salesrpt` program must include two or more arguments. The first argument is the product for the report, and the second and subsequent arguments are the months. In the `LIMIT month` statement, the `&ARGFR(2)` function returns the months that were specified as arguments on the command line.

The following statement executes the `salesrpt` program, specifying `Jan96`, `Feb96`, `Mar96`, and `Apr96` for the values of `month`.

    salesrpt 'Canoes' 'Jan96' TO 'Apr96'

The statement produces the following output.

    PRODUCT: CANOES
            -------------------SALES------------------
            -------------------MONTH------------------
    DISTRICT    Jan96     Feb96      Mar96     Apr96
    ------- ---------- ---------- ---------- ---------
    Boston  70,489.44  82,237.68  97,622.28 134,265.60
    Atlanta 56,271.40  61,828.33  77,217.62 109,253.38
    Chicago 48,661.74  54,424.94  68,815.71  93,045.46
    Dallas  35,244.72  40,218.43  46,810.68  64,031.28
    Denver  44,456.41  50,623.19  57,013.01  78,038.13
    Seattle 67,085.12  74,834.29  87,820.04 119,858.56
           ---------- ---------- ---------- ----------
           322,208.83 364,166.86 435,299.34 598,492.41
           ========== ========== ========== ==========

The following statement specifies the first three months of 1996.

    salesrpt 'Tents' quarter 'Q1.96'

The statement produces the following output.

    PRODUCT: TENTS
                   -------------SALES-------------
                   -------------MONTH-------------
    DISTRICT          Jan96     Feb96      Mar96
    -------------- ---------- ---------- ---------
    Boston         50,808.96  34,641.59  45,742.21
    Atlanta        46,174.92  50,553.52  58,787.82
    Chicago        31,279.78  31,492.35  42,439.52
    Dallas         50,974.46  53,702.75  71,998.57
    Denver         35,582.82  32,984.10  44,421.14
    Seattle        45,678.41  43,094.80  54,164.06
                  ----------  ---------- ---------
                  260,499.35 246,469.11 317,553.32
                  ========== ========== ==========